﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Script.Serialization;

namespace Lottery.Handler
{
    /// <summary>
    /// SetHandler 的摘要说明
    /// </summary>
    public class SetHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";

            String action = context.Request["action"];
            if (action.Equals("Load"))
            {
                string strSQL = "SELECT Id,Name,Qty,Prize,IsVisable FROM LotteryType ;";
                DataSet ds = DbHelper.ExecuteDataSet(System.Data.CommandType.Text, strSQL);
                string strJson = Dataset2Json(ds);//DataSet数据转化为Json数据  
                context.Response.Write(strJson);//返回给前台页面  
                context.Response.End();
            }

            if (action.Equals("Save"))
            {
                string SessionId = "00000" + DbHelper.ExecuteScalar(System.Data.CommandType.Text, "SELECT @@SPID "); //获取SQL的SessionId
                string dg = context.Request["dg"];
                DataTable dt = JsonToDataTable(dg.Replace("\\", "")); //去掉转义字符,json转datatable
                SessionId = SessionId.Substring(SessionId.Length - 6, 6);
                string strSQL = " IF OBJECT_ID('Tb_Temp" + SessionId + "') IS NOT NULL DROP TABLE Tb_Temp" + SessionId + "; "
                            + " SELECT * INTO Tb_Temp" + SessionId + " FROM LotteryType WHERE 1<>1;";
                DbHelper.ExecteNonQuery(System.Data.CommandType.Text, strSQL);

                SqlBulkCopy sbc = new SqlBulkCopy(DbHelper.GetConnSting());
                sbc.DestinationTableName = "Tb_Temp" + SessionId;//指定目标数据库的表名
                //建立数据源表字段和目标表中的列之间的映射
                //sbc.ColumnMappings.Add("Id", "Id");
                sbc.ColumnMappings.Add("Name", "Name");
                sbc.ColumnMappings.Add("Qty", "Qty");
                sbc.ColumnMappings.Add("Prize", "Prize");
                sbc.ColumnMappings.Add("IsVisable", "IsVisable");
                sbc.BulkCopyTimeout = 5000; //超时时间
                sbc.NotifyAfter = 5000; //定义生成通知事件之前 要处理的行数
                //bcp.SqlRowsCopied +=  new SqlRowsCopiedEventHandler(OnRowsCopied);//处理完要处理NotifyAfter的行数时触发的事件
                sbc.WriteToServer(dt); //写入数据库表的物理临时表

                strSQL = "TRUNCATE TABLE LotteryType ";

                strSQL = strSQL + " INSERT INTO LotteryType(Name,Qty,Prize,IsVisable) "
                    + " SELECT Name,Qty,Prize,IsVisable FROM Tb_Temp" + SessionId;

                try
                {
                    int i = DbHelper.ExecteNonQuery(System.Data.CommandType.Text, strSQL);
                    System.Console.Write("执行成功");
                }
                catch (SqlException ex)
                {
                    System.Console.Write("数据操作失败：" + ex.Message);
                }
                finally
                { //删除残留的物理表
                    DbHelper.ExecteNonQuery(System.Data.CommandType.Text, " IF OBJECT_ID('Tb_Temp" + SessionId + "') IS NOT NULL DROP TABLE Tb_Temp" + SessionId + ";");
                }
            }

            if (action.Equals("LType"))
            {
                string strSQL = "SELECT Id,Name FROM LotteryType ;";
                DataSet ds = DbHelper.ExecuteDataSet(System.Data.CommandType.Text, strSQL);
                string strJson = Dataset2Json(ds);//DataSet数据转化为Json数据  
                context.Response.Write(strJson);//返回给前台页面  
                context.Response.End();
            }

            if (action.Equals("Employee"))
            {
                string strSQL = "SELECT Cardid, cardid + '/' + name_cn + '/' + name_en AS Name "
                    + " FROM IDCARD "
                    + " ORDER BY dept, levels, cardid ;";

                DataSet ds = DbHelper.ExecuteDataSet(System.Data.CommandType.Text, strSQL);
                string strJson = Dataset2Json(ds);//DataSet数据转化为Json数据  
                context.Response.Write(strJson);//返回给前台页面  
                context.Response.End();
            }

            if (action.Equals("Submit"))
            {
                string LType = context.Request["LType"];
                string LTypeName = context.Request["LTypeName"];
                string CardId = context.Request["CardId"];
                string Employee = context.Request["Employee"];

                string strSQL = "SELECT ISNULL(MAX(Sort),0) AS MaxId FROM LotteryWinners WHERE LType =@LType ";
                SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@LType", SqlDbType.Int) { Value = LType } };
                int MaxId = (int)DbHelper.ExecuteScalar(System.Data.CommandType.Text, strSQL, parameters);

                strSQL = " INSERT INTO LotteryWinners(LType, LTypeName, CardId, Employee, Sort) "
                            + " SELECT @LType, @LTypeName, @CardId, @Employee, @Sort ";
                SqlParameter[] param = new SqlParameter[] { 
                    new SqlParameter("@LType", SqlDbType.Int) { Value = LType } ,
                    new SqlParameter("@LTypeName", SqlDbType.NVarChar) { Value = LTypeName } ,
                    new SqlParameter("@CardId", SqlDbType.VarChar) { Value = CardId } ,
                    new SqlParameter("@Employee", SqlDbType.NVarChar) { Value = Employee },
                    new SqlParameter("@Sort", SqlDbType.Int) { Value = MaxId+1 } //每次加一
                };
                DbHelper.ExecteNonQuery(System.Data.CommandType.Text, strSQL, param);
            }

            if (action.Equals("Winner"))
            {
                string strSQL = "SELECT b.LType, b.LTypeName, LEFT(IdList, LEN(IdList) - 1) AS CardId,LEFT(NameList, LEN(NameList) - 1) AS Employee "
                        + " FROM ( "
                        + " SELECT LType, LTypeName, "
                        + " (SELECT CardId + ',' FROM LotteryWinners WHERE LType = a.LType FOR XML PATH('') ) AS IdList , "
                        + " (SELECT Employee + ',' FROM LotteryWinners WHERE LType = a.LType FOR XML PATH('') ) AS NameList "
                        + " FROM LotteryWinners a "
                        + " GROUP BY LType, LTypeName ) b ;";
                DataSet ds = DbHelper.ExecuteDataSet(System.Data.CommandType.Text, strSQL);
                string strJson = DataTableToJson(ds.Tables[0]); //Dataset2Json(ds);//DataSet数据转化为Json数据  
                context.Response.Write(strJson);//返回给前台页面  
                context.Response.End();
            }

            if (action.Equals("Delete"))
            {
                string LType = context.Request["LType"];
                string CardId = context.Request["CardId"];
                string strSQL = "DELETE FROM LotteryWinners WHERE LType =@LType AND CardId=@CardId";
                SqlParameter[] parameters = new SqlParameter[] { 
                    new SqlParameter("@LType", SqlDbType.Int) { Value = LType },
                    new SqlParameter("@CardId", SqlDbType.VarChar) { Value = CardId }
                };

                DbHelper.ExecteNonQuery(System.Data.CommandType.Text, strSQL, parameters);
            }

            if (action.Equals("CurrentWinner"))
            {
                string strSQL = "SELECT TOP 1 * FROM LotteryWinners WHERE EXISTS ( SELECT 1 FROM LotteryType WHERE IsVisable = 1 AND Id = LType ) ORDER BY id DESC; ";
                DataSet ds = DbHelper.ExecuteDataSet(System.Data.CommandType.Text, strSQL);
                string strJson = DataTableToJson(ds.Tables[0]); //Dataset2Json(ds);//DataSet数据转化为Json数据  
                context.Response.Write(strJson);//返回给前台页面  
                context.Response.End();
            }

        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }

        /// <summary>  
        /// DataSet转换成Json格式 格式：{total:0,rows:{}}
        /// </summary>    
        /// <param name="ds">DataSet</param>   
        /// <returns></returns>    
        public string Dataset2Json(DataSet ds)
        {
            return Dataset2Json(ds, -1);
        }

        public string Dataset2Json(DataSet ds, int total)
        {
            StringBuilder json = new StringBuilder();

            foreach (DataTable dt in ds.Tables)
            {
                //{"total":5,"rows":[  
                json.Append("{\"total\":");
                if (total == -1)
                {
                    json.Append(dt.Rows.Count);
                }
                else
                {
                    json.Append(total);
                }
                json.Append(",\"rows\":[");
                json.Append(DataTable2Json(dt));
                json.Append("]}");
            } return json.ToString();

        }

        /// <summary>    
        /// dataTable转换成Json格式    
        /// </summary>    
        /// <param name="dt"></param>    
        /// <returns></returns>    
        public static string DataTable2Json(DataTable dt)
        {
            StringBuilder jsonBuilder = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                jsonBuilder.Append("{");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    jsonBuilder.Append("\"");
                    jsonBuilder.Append(dt.Columns[j].ColumnName);
                    jsonBuilder.Append("\":\"");
                    jsonBuilder.Append(dt.Rows[i][j].ToString());
                    jsonBuilder.Append("\",");
                }
                if (dt.Columns.Count > 0)
                {
                    jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
                }
                jsonBuilder.Append("},");
            }
            if (dt.Rows.Count > 0)
            {
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            }

            return jsonBuilder.ToString();
        }

        /// <summary>     
        /// Datatable转换为Json  格式：[{},{}]
        /// </summary>    
        /// <param name="table">Datatable对象</param>     
        /// <returns>Json字符串</returns>     
        public string DataTableToJson(DataTable table)
        {
            StringBuilder JsonString = new StringBuilder();
            if (table.Rows.Count > 0)
            {
                JsonString.Append("[");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    JsonString.Append("{");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        JsonString.Append("}");
                    }
                    else
                    {
                        JsonString.Append("},");
                    }
                }
                JsonString.Append("]");
            }
            return JsonString.ToString();
        }

        /// <summary>
        /// Json 字符串 转换为 DataTable数据集合
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        public static DataTable JsonToDataTable(string json)
        {
            DataTable dataTable = new DataTable();  //实例化
            DataTable result;
            try
            {
                JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
                javaScriptSerializer.MaxJsonLength = Int32.MaxValue; //取得最大数值
                ArrayList arrayList = javaScriptSerializer.Deserialize<ArrayList>(json);
                if (arrayList.Count > 0)
                {
                    foreach (Dictionary<string, object> dictionary in arrayList)
                    {
                        if (dictionary.Keys.Count == 0)
                        {
                            result = dataTable;
                            return result;
                        }
                        if (dataTable.Columns.Count == 0)
                        {
                            foreach (string current in dictionary.Keys)
                            {
                                dataTable.Columns.Add(current, dictionary[current].GetType());
                            }
                        }
                        DataRow dataRow = dataTable.NewRow();
                        foreach (string current in dictionary.Keys)
                        {
                            dataRow[current] = dictionary[current];
                        }

                        dataTable.Rows.Add(dataRow); //循环添加行到DataTable中
                    }
                }
            }
            catch
            {
            }
            result = dataTable;
            return result;
        }

    }
}